﻿-------4. EBANK
--  A	Hoạt động
--B	Đợi kích hoạt ok
--W	Chờ duyệt đăng ký ok
--U	Chờ duyệt thay đổi
--C	Hủy đăng ký ok
--I	Ngừng hoạt động ok
--R	Từ chối duyệt ok
--D	History ( trạng thái nối)ok
--H	Hủy
declare @date_update nvarchar(100)= (select [DATE_UPDATE] from [DATE_EM_REPORT])
exec('
SELECT  BUSINESS_DATE='''+@date_update+''' ,CUST_CIF 
,CASE WHEN AMND_STATUS =''A''  THEN ''I2B_ACTIVE''
WHEN AMND_STATUS IN (''B'',''W'')  THEN ''I2B_WAIT''
WHEN AMND_STATUS IN (''C'',''H'',''I'')  THEN ''I2B_CANCEL''
ELSE ''I2B_OTHERS'' END I2B_STATUS
,USER_NAME,OPEN_DATE,amnd_date,AMND_STATUS 
,ROW_NUMBER() over(partition by (cust_cif+user_name) order by amnd_date desc) as RN 
INTO #I2B_ALL_DAY
FROM [SERVER16].[VPB_WHR2].DBO.TBL_B2B_USERS IU
WHERE CUST_CIF IN (SELECT CIF FROM TBL_CUSTOMER )
order by amnd_date

--IF EXISTS
--(SELECT *
--FROM DBO.SYSOBJECTS O
--WHERE O.XTYPE IN (''U'') AND O.NAME = ''TBL_CM_I2B_DETAIL'')
Delete from TBL_CM_I2B_DETAIL

Insert TBL_CM_I2B_DETAIL
SELECT * , CASE WHEN AMND_STATUS IN (''A'',''B'',''W'') THEN 1 ELSE 0 END ''ACTIVE_STATUS'' --into TBL_CM_I2B_DETAIL
FROM #I2B_ALL_DAY
WHERE RN=1

IF EXISTS
(SELECT *
FROM DBO.SYSOBJECTS O
WHERE O.XTYPE IN (''U'') AND O.NAME = ''TBL_CM_I2B'')
DROP TABLE TBL_CM_I2B


SELECT a.BUSINESS_DATE,b.CIF, SUM(RN ) AS NO_USER, SUM(ACTIVE_STATUS) AS NO_USER_ACTIVE ,b.CUS_NAME, b.INDUSTRY_NAME, b.INDUSTRY_NAME_EN, b.CUS_OPEN_DATE, b.DAO, b.dao_name, c.BRANCH_ID, c.BRANCH_CODE_SME, c.BRANCH_NAME_SME, c.REGION
into TBL_CM_I2B
FROM TBL_CM_I2B_DETAIL a, TBL_CUSTOMER b, TBL_BRANCH c
where a.CUST_CIF=b.CIF and b.BRANCH_ID = c.BRANCH_ID
GROUP BY b.CIF,a.BUSINESS_DATE,b.CUS_NAME, b.INDUSTRY_NAME, b.INDUSTRY_NAME_EN, b.CUS_OPEN_DATE, b.DAO, b.dao_name, c.BRANCH_ID, c.BRANCH_CODE_SME, c.BRANCH_NAME_SME, c.REGION

--SELECT BUSINESS_DATE,CUST_CIF AS CIF, SUM(RN ) AS NO_USER, SUM(ACTIVE_STATUS) AS NO_USER_ACTIVE 
--into TBL_CM_I2B
--FROM TBL_CM_I2B_DETAIL
--GROUP BY CUST_CIF,BUSINESS_DATE
')
--select * from TBL_CM_I2B_DETAIL
--select * from TBL_CM_I2B
--drop table TBL_CM_I2B
